package com.example.sqldemo;

import android.util.Log;

import java.sql.*;

public class DBConnection {

    //add增
    public static void add (String username, String password){
        //Log.i("temp", username);
        //Log.i("temp", password);
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "INSERT INTO SQLDemoDB (username, password) VALUES ('" + username + "','" + password + "')";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.execute(sql);
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    //update改
    public static void update (String username, String newpassword){
        //Log.i("temp", username);
        //Log.i("temp", newpassword);
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "UPDATE SQLDemoDB SET password='" + newpassword + "' WHERE username='" + username + "';";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.execute(sql);
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    //query查
    public static Boolean query (String username, String password){
        Boolean flag = false;
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "SELECT password FROM SQLDemoDB WHERE username='" + username + "';";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String realpassword = rs.getString("password");
                //Log.i("temp", realpassword);
                if(realpassword.equals(password)){
                    flag = true;
                }
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        Log.i("temp", String.valueOf(flag));
        return flag;
    }

    //delete删
    public static void delete (String username, String password){
        //Log.i("temp", username);
        //Log.i("temp", password);
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "DELETE FROM SQLDemoDB WHERE username='" + username + "' AND password='" + password + "';";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.execute(sql);
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    //检查用户名是否存在
    public static Boolean check (String username){
        //Log.i("temp", username);
        Boolean flag = false;
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "SELECT * FROM SQLDemoDB WHERE username='" + username + "';";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            if(!rs.first()){
                flag = false;
            }
            else{
                flag = true;
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        //Log.i("temp", String.valueOf(flag));
        return flag;
    }

    //连接数据库模板（与上面类似，只是增加了注释）
    /*public static void link (){
        //要连接的数据库url,注意：此处连接的应该是服务器上的MySQl的地址
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        //连接数据库使用的用户名
        String userName = "test";
        //连接的数据库时使用的密码
        String password = "q1w2e3r4_";
        Connection connection=null;
        try {
            //1、加载驱动
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            System.out.println("驱动加载成功！！！");
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            //2、获取与数据库的连接
            connection = DriverManager.getConnection(url, userName, password);
            System.out.println("连接数据库成功！！！");
            //3.sql语句
            String sql = "SELECT * FROM dataForTest";
            //4.获取用于向数据库发送sql语句的ps
            PreparedStatement ps=connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String record = rs.getString("record");
                Log.i("temp", record);
            }
            rs.close();
            ps.close();;
            connection.close();

        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }

    }*/

}
